import concurrent.futures
import json
import requests
import time
import numpy as np

from src.utils.config import logger, config
from src.utils.db_processor import mysql_prediction_processor,presto_processor
# from src.utils.dingding_message import dingding_messager
from src.utils.feishu_message import feishu_messager
from src.utils.redis_pool import redis_conn
from src.utils.util import get_today,sending_exception,sending_b2b_exception
from tensorflow.python.ops import math_ops
from tensorflow.python.keras import backend as K
# import timeout_decorator

# B端API接口地址
B_API_SERVER = config.get_config('project', 'b_api_server')
REMOTE_PRODUCT_NAME_URL = '{}/product'.format(B_API_SERVER)
REMOTE_BRAND_NAME_URL = '{}/brand'.format(B_API_SERVER)
REMOTE_SKU_PROPERTY_URL = '{}/ppn/skuproperty/sku'.format(B_API_SERVER)
# 调取接口
TIMEOUT_SETTING = 1
MIN_EVN_PRICE=5

def getMape(y_predict_data,y_true):
    predict_data = np.round(y_predict_data.reshape((-1, 1))).flatten().astype(int)

    margin=np.abs(
        (np.clip(predict_data, MIN_EVN_PRICE, None) - np.clip(y_true, MIN_EVN_PRICE, None)))
    # recent_predict_data=[x[0] for x in recent_predict_data]
    mape = np.mean(100. *np.abs(margin/np.clip(y_true, MIN_EVN_PRICE, None)))



    rmse = np.sqrt(np.mean(np.square(margin)))
    mae = np.mean(margin)

    return rmse,mae,mape

def custom_mean_absolute_percentage(y_true, y_pred):
    diff = math_ops.abs(
        (K.clip(y_true, 1e-7, None) - y_pred) / K.clip(y_true, 1e-7, None))
    return 100. * K.mean(diff, axis=-1)


def cal_period(s, relative_date=None, max_period=100, interval=30):
    """
    计算周期，30天为一周期
    :param s: 日期序列
    :param relative_date: 当前日期，默认为当天日期
    :param max_period: 最大周期数
    :param interval: 周期间隔
    :return:
    """
    if relative_date is None:
        relative_date = get_today()
    diff_days = relative_date - s
    diff_days = diff_days.days
    period = diff_days // interval
    return min(period, max_period)

def get_delete_column_product_id_to_train_data(category_id):
    """
    获取等级ID和名称的映射
    :param category_id: 类别ID
    :return:
    """
    sql = """
    select * from sku_level_train_delete_column 
    where status=1 and product_category_id={} and column_name is not null and start_date<=now() and end_date>=now()""".format(category_id)
    add_names = mysql_prediction_processor.load_sql(sql)
    logger.info('get_add_column_product_id has data size ={}'.format(add_names.shape[0]))
    return add_names

def get_add_column_product_id(category_id):
    """
    获取等级ID和名称的映射
    :param category_id: 类别ID
    :return:
    """
    sql = """
    select * from sku_level_add_column 
    where status=1 and product_category_id={} and column_name is not null and start_date<=now() and end_date>=now()""".format(category_id)
    add_names = mysql_prediction_processor.load_sql(sql)
    logger.info('get_add_column_product_id has data size ={}'.format(add_names.shape[0]))
    return add_names

def get_oversampling_product_id(category=1,date='2020-01-01'):
    """
    获取等级ID和名称的映射
    :param category_id: 类别ID
    :return:(4,7,9,16,52,184,484)
    """
    sql = """
    select product_id,array_join(array_agg(property_value_name),'_')  as property_value_name from (
        select distinct dp.product_id,trim(pssp.property_value_name) as property_value_name 
        FROM dim.dim_product dp 
        join dim.dim_product_sku_sub_product pssp on dp.product_id=pssp.product_id
        where pssp.property_name IN ( '颜色','机身颜色') 
              and  dp.product_create_date>cast('{}'  as date) and dp.product_category_id={}
            and  dp.product_id_status_id=2 and dp.active=1 
    )a
    group by product_id """
    product_id_mapping = presto_processor.load_sql(sql.format(date, category))
    product_color_map={r[0]:  {i: val for i, val in enumerate(r[1].split('_'))} for r in product_id_mapping.itertuples(index=False)}
    return product_color_map

def get_level_id_mapping(category_id):
    """
    获取等级ID和名称的映射
    :param category_id: 类别ID
    :return:
    """
    sql = """
    SELECT product_level_id, product_level_name 
    FROM dim.dim_product_level 
    WHERE product_category_id = {}""".format(category_id)
    level_id_mapping = presto_processor.load_sql(sql)
    return {r[0]: r[1] for r in level_id_mapping.itertuples(index=False)}

def get_level_id_mapping_df(category_id):
    """
    获取等级ID和名称的映射
    :param category_id: 类别ID
    :return:
    """
    sql = """
    SELECT product_level_id as level_id, product_level_name 
    FROM dim.dim_product_level 
    WHERE product_category_id = {}""".format(category_id)
    level_id_mapping = presto_processor.load_sql(sql)

    return level_id_mapping

def get_laptop_discont_mapping():
    """
    获取等级ID和名称的映射
    :param category_id: 类别ID
    :return:
    """
    sql = """
    select product_brand_id,final_discont_rate from algorithm_model_c_laptop_discont 
    where settle_document_create_date=DATE_FORMAT(DATE_SUB(date('{}') ,INTERVAL 1 DAY),'%Y-%m-%d')
    """.format(get_today())
    brand_discont_mapping = mysql_prediction_processor.load_sql(sql)
    if (brand_discont_mapping is None) or (brand_discont_mapping.empty):
        return {}

    return {r[0]: r[1] for r in brand_discont_mapping.itertuples(index=False)}

def get_laptop_discont_api_mapping():
    """
    获取等级ID和名称的映射:API专用，eg:因为discont表 是2号更新：取数来自1号，但是api的重新加载是3号，所以此处取数的时间应该-2
    :param category_id: 类别ID
    :return:
    """
    sql = """
    select product_brand_id,final_discont_rate from algorithm_model_c_laptop_discont 
    where settle_document_create_date=DATE_FORMAT(DATE_SUB(date('{}') ,INTERVAL 2 DAY),'%Y-%m-%d')
    """.format(get_today())
    brand_discont_mapping = mysql_prediction_processor.load_sql(sql)
    if (brand_discont_mapping is None) or (brand_discont_mapping.empty):
        return {}

    return {r[0]: r[1] for r in brand_discont_mapping.itertuples(index=False)}

def get_level_template_mapping():
    """
    读取等级模板和等级对应关系
    :return:
    """
    LEVEL_TEMPLATE_MAPPING_SQL = """
    select
    product_id,secondary_level_template_id as product_level_template_id
    from 
    ods.ods_opt_foundation_secondary_product_template_mapping
    """

    mapping = presto_processor.load_sql(LEVEL_TEMPLATE_MAPPING_SQL)
    # mapping['product_id'] = mapping['product_id'].astype(int)
    # mapping['secondary_level_template_id'] = mapping['secondary_level_template_id'].astype(str)
    # return {r[0]: r[1] for r in mapping.itertuples(index=False)}
    return mapping


def load_sku_property_bak(sku_data, redis_prefix, columns_mapping=None):
    """
    从redis缓存中读取sku的特征
    :param sku_data: 输入sku数据
    :param redis_prefix: redis的key前缀
    :param columns_mapping: 列名映射
    :return:
    """
    logger.info('loading sku property')
    for sku in sku_data:
        sku_property = redis_conn.hgetall(
            redis_prefix + str(sku.get('sku_id')))
        if not sku_property and columns_mapping:
            sku_property = process_remote_sku_property_bak(sku, columns_mapping)
            if sku_property is None:
                sending_exception('无法获取sku特征数据：{} ！'.format(sku))
                continue
        sku.update(sku_property)
    return sku_data


def process_remote_sku_property_bak(sku, columns_mapping):
    """
    处理需需要调用的SKU特征
    :param sku: 输入sku数据
    :param columns_mapping: 变量映射
    :return:
    """
    logger.info('reading remote sku property sku@{}'.format(sku))
    product_name, product_brand_name, sku_prop = get_remote_sku_property(sku)
    if product_name is None or product_brand_name is None or sku_prop is None:
        return None
    sku_property = {'product_name': product_name,
                    'product_brand_name': product_brand_name}
    sku_prop = {columns_mapping[k]: v for k,
                v in sku_prop.items() if k in columns_mapping.keys()}
    sku_property.update(sku_prop)

    return sku_property


def get_remote_sku_property(sku):
    """
    从B端接口查询sku的特征
    :param sku: 输入sku数据
    :return:
    """
    params = [sku.get('product_id'), sku.get('brand_id'), sku.get('sku_id')]
    # 检查所有参数是否齐全
    if not all(params):
        msg = '调用B2B接口参数不齐全! product_id@{}, brand_id@{}, sku_id@{}'.format(
            *params)
        logger.critical(msg)
        #dingding_messager.send_message(msg)
        return {}

    remote_sku_property = []
    # res1=load_remote_product_name(102)
    # res2=load_remote_brand_name(105)
    # res3=load_remote_sku_property(12)
    with concurrent.futures.ThreadPoolExecutor(max_workers=3) as executor:
        tasks = [executor.submit(f, p) for f, p in zip([load_remote_product_name, load_remote_brand_name,
                                                        load_remote_sku_property], params)]
    for t in tasks:
        try:
            result = t.result()
            if result is None:
                return None
            remote_sku_property.append(result)
        except Exception as e:
            logger.error('调用B2B接口获取sku属性发生异常：{}'.format(e))
            sending_b2b_exception(
                '调用B2B接口获取sku属性发生异常：{}'.format(e))
            return None
    return remote_sku_property



def get_remote_sku_property_gc(sku):
    """
    从B端接口查询sku的特征
    :param sku: 输入sku数据
    :return:
    """
    params = [sku.get('productId'), sku.get('brandId'), sku.get('skuId')]
    # 检查所有参数是否齐全
    if not all(params):
        msg = '调用B2B接口参数不齐全! product_id@{}, brand_id@{}, sku_id@{}'.format(
            *params)
        logger.critical(msg)
        #dingding_messager.send_message(msg)
        return {}

    remote_sku_property = []
    # res1=load_remote_product_name(102)
    # res2=load_remote_brand_name(105)
    # res3=load_remote_sku_property(12)
    with concurrent.futures.ThreadPoolExecutor(max_workers=3) as executor:
        tasks = [executor.submit(f, p) for f, p in zip([load_remote_product_name, load_remote_brand_name,
                                                        load_remote_sku_property], params)]
    for t in tasks:
        try:
            result = t.result()
            if result is None:
                return None
            remote_sku_property.append(result)
        except Exception as e:
            logger.error('调用B2B接口获取sku属性发生异常：{}'.format(e))
            sending_b2b_exception(
                '调用B2B接口获取sku属性发生异常：{}'.format(e))
            return None
    return remote_sku_property


def load_remote_product_name(product_id):
    """
    从B端接口获取产品名称
    :param product_id: 产品ID
    :return:
    """
    try:
        response = requests.get(
            '{}/{}'.format(REMOTE_PRODUCT_NAME_URL, product_id), timeout=TIMEOUT_SETTING)
        resp = json.loads(response.content)

        if (resp['code'] != 200):
            msg = 'product_id={}获取不到信息，原因：{}'.format(resp['resultMessage'])
            logger.error(msg + '{}'.format(resp['resultMessage']))
            sending_b2b_exception(msg)
            return None
        return resp['data']['name']
    except Exception as e:
        logger.error('调用B2B接口获取产品名称发生异常：{}'.format(e))
        sending_b2b_exception('调用B2B接口获取产品名称发生异常：{}'.format(e))
        return None


def load_remote_brand_name(brand_id):
    """
    从B端接口获取品牌名称
    :param brand_id: 品牌ID
    :return:
    """
    try:
        response = requests.get(
            '{}/{}'.format(REMOTE_BRAND_NAME_URL, brand_id), timeout=TIMEOUT_SETTING)
        resp = json.loads(response.content)
        if (resp['code'] != 200):
            msg = 'brand_id={}获取不到信息，原因：{}'.format(resp['resultMessage'])
            logger.error(msg + '{}'.format(resp['resultMessage']))
            sending_b2b_exception(msg)
            return None
        return resp['data']['name']
    except Exception as e:
        logger.error('调用B2B接口获取品牌名称发生异常：{}'.format(e))
        sending_b2b_exception('调用B2B接口获取品牌名称发生异常：{}'.format(e))
        return None


def load_remote_sku_property(sku_id):
    """
    从B端接口获SKU属性
    :param sku_id: SKU ID
    :return:
    """
    try:
        response = requests.get(
            '{}/{}'.format(REMOTE_SKU_PROPERTY_URL, sku_id), timeout=TIMEOUT_SETTING)
        content = response.content
        resp = json.loads(content)

        if (resp['code'] != 200):
            msg = 'sku_id={}获取不到信息，原因：{}'.format(resp['resultMessage'])
            logger.error(msg + '{}'.format(resp['resultMessage']))
            sending_b2b_exception(msg)
            return None
        properties = {d['name'].strip(): d['pricePropertyValues'][0]['value']
                      for d in resp['data']}
        return properties
    except Exception as e:
        logger.error('调用B2B接口获取sku属性发生异常：{}'.format(e))
        sending_b2b_exception('调用B2B接口获取sku属性发生异常：{}'.format(e))
        return None


def load_history_avg_price(key):
    """
    读取均价均价
    :param key: key
    :return:
    """
    price = redis_conn.get(key)
    if price is None:
        return price
    return int(float(price))

# @timeout_decorator.timeout(1.5,use_signals=False)
def load_sku_property(sku_data, redis_prefix, columns_mapping=None):
    """
    从redis缓存中读取sku的特征
    :param sku_data: 输入sku数据
    :param redis_prefix: redis的key前缀
    :param columns_mapping: 列名映射
    :return:
    """
    logger.info('loading sku property')
    t0=time.time()
    if len(sku_data) < 5:
        sku_data_redis = []
        sku_data_remote = []
        for sku in sku_data:
            sku_property = redis_conn.hgetall(
                redis_prefix + str(sku.get('sku_id')))
            if sku_property:
                sku.update(sku_property)
                sku_data_redis.append(sku)
            else:
                sku_data_remote.append(sku)
        logger.info('for each skuid by redis use time :{}'.format(time.time()-t0))
        starttime = time.time()
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            tasks = [executor.submit(process_remote_sku_property, sku_sub, columns_mapping) for sku_sub in sku_data_remote]
        endtime = time.time()
        logger.info("loading remote sku property use time@{}".format(endtime - starttime))
        res = []
        try:
            if len(tasks) != 0:
                for t in tasks:
                    print(t.result())
                    res.append(t.result())

        except Exception as e:
            logger.critical('load_sku_property tasks 处理异常 ：{}'.format(e))
        res.extend(sku_data_redis)
        logger.info('res={}'.format(res))
        return res
    else:
        sku_distinct = []
        sku_data_distinct = []
        for sku in sku_data:
            sku_id = sku['sku_id']
            if sku_id not in sku_distinct:
                sku_distinct.append(sku_id)
                sku_tmp = sku.copy()
                del sku_tmp['level_id']
                sku_data_distinct.append(sku_tmp)
            else:
                continue
        sku_data_redis_distinct_dict = {}
        sku_data_remote_distinct_list = []
        sku_data_remote_distinct_dict = {}
        for sku in sku_data_distinct:
            sku_property = redis_conn.hgetall(
                redis_prefix + str(sku.get('sku_id')))
            if sku_property:
                #sku.update(sku_property)
                sku_data_redis_distinct_dict[sku['sku_id']] = sku_property
            else:
                sku_data_remote_distinct_list.append(sku)
        starttime = time.time()
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            tasks = [executor.submit(process_remote_sku_property, sku_sub, columns_mapping) for sku_sub in sku_data_remote_distinct_list]
        endtime = time.time()
        logger.info("loading remote sku property use time@{}".format(endtime - starttime))
        if len(tasks) != 0:
            for t in tasks:
                print(t.result())
                data_sub = t.result()
                sku_data_remote_distinct_dict[data_sub['sku_id']] = data_sub
        sku_property_map = {**sku_data_redis_distinct_dict, **sku_data_remote_distinct_dict}
        for sku in sku_data:
            sku_id = sku['sku_id']
            sku.update(sku_property_map[sku_id])
        return sku_data

def load_sku_property_gc(sku_data, redis_prefix, columns_mapping=None):
    """
    从redis缓存中读取sku的特征
    :param sku_data: 输入sku数据
    :param redis_prefix: redis的key前缀
    :param columns_mapping: 列名映射
    :return:
    """
    logger.info('loading sku property')
    t0=time.time()
    if len(sku_data) < 5:
        sku_data_redis = []
        sku_data_remote = []
        for sku in sku_data:
            sku_property = redis_conn.hgetall(
                redis_prefix + str(sku.get('skuId')))
            if sku_property:
                sku.update(sku_property)
                sku_data_redis.append(sku)
            else:
                sku_data_remote.append(sku)
        logger.info('for each skuid by redis use time :{}'.format(time.time()-t0))
        starttime = time.time()
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            tasks = [executor.submit(process_remote_sku_property_gc, sku_sub, columns_mapping) for sku_sub in sku_data_remote]
        endtime = time.time()
        logger.info("loading remote sku property use time@{}".format(endtime - starttime))
        res = []
        try:
            if len(tasks) != 0:
                for t in tasks:
                    print(t.result())
                    res.append(t.result())

        except Exception as e:
            logger.critical('load_sku_property tasks 处理异常 ：{}'.format(e))
        res.extend(sku_data_redis)
        logger.info('res={}'.format(res))
        return res
    else:
        sku_distinct = []
        sku_data_distinct = []
        for sku in sku_data:
            sku_id = sku['skuId']
            if sku_id not in sku_distinct:
                sku_distinct.append(sku_id)
                sku_tmp = sku.copy()
                del sku_tmp['levelId']
                del sku_tmp['itemCode']
                del sku_tmp['propertyValues']
                sku_data_distinct.append(sku_tmp)
            else:
                continue
        sku_data_redis_distinct_dict = {}
        sku_data_remote_distinct_list = []
        sku_data_remote_distinct_dict = {}
        for sku in sku_data_distinct:
            sku_property = redis_conn.hgetall(
                redis_prefix + str(sku.get('skuId')))
            if sku_property:
                #sku.update(sku_property)
                sku_data_redis_distinct_dict[sku['skuId']] = sku_property
            else:
                sku_data_remote_distinct_list.append(sku)
        starttime = time.time()
        with concurrent.futures.ThreadPoolExecutor(max_workers=5) as executor:
            tasks = [executor.submit(process_remote_sku_property_gc, sku_sub, columns_mapping) for sku_sub in sku_data_remote_distinct_list]
        endtime = time.time()
        logger.info("loading remote sku property use time@{}".format(endtime - starttime))
        errNum = 0
        errSkuList = []
        if len(tasks) != 0:
            for t in tasks:
                exception = t.exception()
                if exception is not None:
                    errNum+=1
                    continue
                print(t.result())
                data_sub = t.result()
                sku_data_remote_distinct_dict[data_sub['skuId']] = data_sub
        sku_property_map = {**sku_data_redis_distinct_dict, **sku_data_remote_distinct_dict}

        sku_data_extend=[]
        for sku in sku_data:
            sku_id = sku['skuId']
            skuPropertyData=sku_property_map.get(sku_id)
            if skuPropertyData is  None:
                level_id = sku.get('levelId')
                spuid = str(sku_id )+ '_' + str(level_id)
                errSkuList.append(spuid)
                continue
            sku.update(skuPropertyData)
            sku_data_extend.append(sku)
        logger.info("goods_code laptop error errNum={},errSpuidList={}".format(errNum,errSkuList))
        return sku_data_extend


def process_remote_sku_property(sku, columns_mapping):
    """
    处理需需要调用的SKU特征
    :param sku: 输入sku数据
    :param columns_mapping: 变量映射
    :return:
    """
    logger.info('reading remote sku property sku@{}'.format(sku))

    product_name, product_brand_name, sku_prop = get_remote_sku_property(sku)

    if product_name is None or product_brand_name is None or sku_prop is None:
        return None
    sku_property = {'product_name': product_name,
                    'product_brand_name': product_brand_name}
    sku_prop = {columns_mapping[k]: v for k,
                v in sku_prop.items() if k in columns_mapping.keys()}
    sku_property.update(sku_prop)
    sku.update(sku_property)
    return sku



def process_remote_sku_property_gc(sku, columns_mapping):
    """
    处理需需要调用的SKU特征
    :param sku: 输入sku数据
    :param columns_mapping: 变量映射
    :return:
    """
    logger.info('reading remote sku property sku@{}'.format(sku))

    product_name, product_brand_name, sku_prop = get_remote_sku_property_gc(sku)

    if product_name is None or product_brand_name is None or sku_prop is None:
        return None
    sku_property = {'product_name': product_name,
                    'product_brand_name': product_brand_name}
    sku_prop = {columns_mapping[k]: v for k,
                v in sku_prop.items() if k in columns_mapping.keys()}
    sku_property.update(sku_prop)
    sku.update(sku_property)
    return sku


if __name__ == "__main__":
    import time
    MOBILE_COLUMNS_MAPPING = {'内存': 'memory', '苹果保修期时长': 'guarantee', '存储容量': 'storage', '网络制式': 'networks',
                              '购买渠道': 'purchase_way', '机身颜色': 'color', '小型号': 'small_version'}
    data = [{'sku_id': 9759, 'level_id': 91, 'product_id': 8493, 'brand_id': 8},
            {'sku_id': 9759, 'level_id': 91, 'product_id': 8493, 'brand_id': 8},
            {'sku_id': 9759, 'level_id': 91, 'product_id': 8493, 'brand_id': 8},
            {'sku_id': 9759, 'level_id': 91, 'product_id': 8493, 'brand_id': 8},
            {'sku_id': 9759, 'level_id': 91, 'product_id': 8493, 'brand_id': 8},
            {'sku_id': 9759, 'level_id': 91, 'product_id': 8493, 'brand_id': 8},
            {'sku_id': 9759, 'level_id': 91, 'product_id': 8493, 'brand_id': 8},
            {'sku_id': 102285, 'level_id': 432, 'product_id': 25239, 'brand_id': 484},
            {'sku_id': 102285, 'level_id': 433, 'product_id': 25239, 'brand_id': 484},
            {'sku_id': 1290400007, 'level_id': 529, 'product_id': 6421, 'brand_id': 7}]
    t1 = time.time()
    sku_property = load_sku_property(data, '3050_', MOBILE_COLUMNS_MAPPING)
    t2 = time.time()
    print(t2 - t1)
    print(sku_property)
    t3 = time.time()
    sku_property1 = load_sku_property(data, '3050_', MOBILE_COLUMNS_MAPPING)
    t4 = time.time()
    print(t4-t3)
    print(sku_property1)